package org.demo.db.xsd;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.demo.xml.XmlTool;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
 * 工具类：database table -> schema
 * @author  
 * @date    2010-8-30
 * @file    org.gen.db.XsdGenerator.java
 */
public class XsdGenerator {
	private DbEl db = new DbEl();
	private final String NS_PREFIX;
	private ClassLoader driverLoader = null;
	
	/**
	 * 构造方法
	 */
	public XsdGenerator(){
		NS_PREFIX = "";
	}
	public XsdGenerator(String ns_prefix){
		NS_PREFIX = ns_prefix;
	}
	/**
	 * 初始化
	 * @param filename
	 * @throws Exception
	 */
	public void generate(String filename)throws Exception{
		InputStream is = new FileInputStream(filename);
		// get root element
		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		DocumentBuilder builder = factory.newDocumentBuilder();
		Document document = builder.parse(is);
		Element root = document.getDocumentElement();
		// get db element
		Element xdbEl = XmlTool.getChildren(root, "db").get(0);
		Element xexportEl = XmlTool.getChildren(xdbEl, "export").get(0);
		db.setDriver(xdbEl.getAttribute("driver"));
		db.setUrl(xdbEl.getAttribute("url"));
		db.setUserid(xdbEl.getAttribute("userid"));
		db.setPassword(xdbEl.getAttribute("password"));
		db.setSchema(xdbEl.getAttribute("schema"));
		db.setDest(xexportEl.getAttribute("dest"));
		db.setBgFileName(xexportEl.getAttribute("bgFileName"));
		Element xtableEl = XmlTool.getChildren(xexportEl, "table").get(0);
		TableEl table = new TableEl();
		table.setName(xtableEl.getAttribute("name"));
		table.setFileName(xtableEl.getAttribute("fileName"));
		table.setOwnerShip(xtableEl.getAttribute("ownerShip"));
		table.setFields(getFields(xtableEl));
		table.setTables(getSubTables(xtableEl));
		db.setTable(table);
		// --
		db2Csv(db);
	}
	/**
	 * 由数据库 -> 抽取元数据信息 -> 生成 schema
	 * @param db
	 * @return
	 * @exception IOException
	 * @exception RuntimeException
	 */
	private void db2Csv(DbEl db)throws IOException{
		// get connection
		Connection conn = getConnection(db);
		TableEl rootTable = db.getTable();
		// generate tables xsd
		genTablesXsd(db.getDest(),rootTable,conn,db);
		// generate bg xsd
		genBgXsd(db.getDest(),db.getBgFileName(),rootTable);
	}
	/**
	 * 生成 schema 文件
	 * @param dest
	 * @param csvLines
	 * @param table
	 * @exception IOException
	 */
	private void csv2Xsd(String dest, List<Line> csvLines, TableEl table)
		throws IOException{
		File dir = new File(dest);
		if (!dir.exists()){
			dir.mkdirs();
		}
		String xml = genTableXsdContent(csvLines,table);
		File xsdFile = new File(dir,table.getFileName() + ".xsd");
		if (!xsdFile.exists()){
			xsdFile.createNewFile();
		}
		PrintWriter out = new PrintWriter(xsdFile);
		out.print(xml);
		out.flush();
		out.close();
	}
	
	//-----------------------------------------------------------------//
	/**
	 * 以递归方式 获取所有子表
	 * @param table
	 * @return
	 */
	private List<TableEl> getSubTables(Element xtable){
		List<TableEl> list = new ArrayList<TableEl>();
		List<Element> xtableList = XmlTool.getChildren(xtable, "table");
		for (int i=0;i<xtableList.size();i++){
			Element t = xtableList.get(i);			
			// --
			TableEl table = new TableEl();
			table.setName(t.getAttribute("name"));
			table.setFileName(t.getAttribute("fileName"));
			table.setOwnerShip(t.getAttribute("ownerShip"));
			table.setFields(getFields(t));
			table.setTables(getSubTables(t));
			// 
			list.add(table);
		}
		return list;
	}
	/**
	 * 获取所有 field 子元素
	 * @param table
	 * @return
	 */
	private Map<String,FieldEl> getFields(Element table){
		Map<String,FieldEl> fields = new HashMap<String,FieldEl>();
		List<Element> xfieldList = XmlTool.getChildren(table, "field");
		for (int j=0;j<xfieldList.size();j++){
			Element f = xfieldList.get(j);
			FieldEl field = new FieldEl();
			field.setName(f.getAttribute("name").toUpperCase());
			field.setForeignKey(f.getAttribute("foreignKey"));
			field.setPrimaryKey(Boolean.valueOf(f.getAttribute("isPrimaryKey")));
			field.setUid(f.getAttribute("uid"));
			fields.put(field.getName(),field);
		}
		return fields;
	}
	/**
	 * 获取 数据库连接
	 * @param db
	 * @return
	 * @exception RuntimeException
	 */
	private Connection getConnection(DbEl db){
		Connection conn = null;
		try {
			Class.forName(db.getDriver());
			conn = DriverManager.getConnection(
					             db.getUrl(), 
					             db.getUserid(), 
					             db.getPassword());
		} catch(Exception e){
			if (driverLoader != null){
				try {
					Class<?> dc = Class.forName(
							         db.getDriver(), 
							         true, 
							         driverLoader);
					Properties info = new Properties();
					info.put("user", db.getUserid());
					info.put("password", db.getPassword());
					Driver driver = (Driver)dc.newInstance();
					conn = driver.connect(db.getUrl(), info);
				} catch (Exception cep){
					throw new RuntimeException(cep.getMessage());
				}
			}
		}
		return conn;
	}
	/**
	 * 生成数据库表的 schema
	 * @param dest
	 * @param table
	 * @param conn
	 * @param db
	 * @exception IOException
	 * @exception RuntimeException
	 */
	private void genTablesXsd(String dest,TableEl table,Connection conn,DbEl db)
		throws IOException{
		// 1. generate current table
		List<Line> csvLines = analyseTable(conn,table,db);
		final Pattern p = Pattern.compile("(\\w*[^\\d])(\\d+)$");
		Collections.sort(csvLines, new Comparator<Line>(){
			public int compare(Line o1, Line o2) {
				String current = o1.getColumnName().toLowerCase();
				String next = o2.getColumnName().toLowerCase();
				Matcher m1 = p.matcher(current);
				Matcher m2 = p.matcher(next);
				if (m1.find() && m2.find()
						&& m1.group(1).equals(m2.group(1))){
					// 判断前缀相同数字结尾的情况
					int first = Integer.parseInt(m1.group(2));
					int second = Integer.parseInt(m2.group(2));
					return first - second;
				}
				// 将扩展字段 extend** 置于最后
				if (!current.startsWith("extend") && next.startsWith("extend")){
					return -1;
				}
				if (current.startsWith("extend") && !next.startsWith("extend")){
					return 1;
				}
				// 普通字符串比较
				return current.compareTo(next);
			}
		});
		csv2Xsd(dest,csvLines,table);
		// 2. generate sub tables
		List<TableEl> tableList = table.getTables();
		for (int i=0; i<tableList.size(); i++){
			TableEl t = tableList.get(i);
			genTablesXsd(dest,t,conn,db);
		}
	}
	/**
	 * 生成 bg schema 文件
	 * @param dest
	 * @param bgFileName
	 * @param table
	 * @exception IOException
	 */
	private void genBgXsd(String dest, String bgFileName, TableEl table)
		throws IOException{
		File xsdFile = new File(dest,bgFileName + ".xsd");
		if (!xsdFile.exists()){
			xsdFile.createNewFile();
		}
		String tableName = table.getName();
		StringBuilder sb = new StringBuilder();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>").append('\n');
		sb.append("<xsd:schema xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"").append('\n');
		sb.append("            targetNamespace=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/" + NS_PREFIX + tableName.toLowerCase()+ "bg\"").append('\n');
		sb.append("            xmlns:" + tableName.toLowerCase() + "=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/" + NS_PREFIX + tableName.toLowerCase()+ "\"").append('\n');
		sb.append("            xmlns:bo=\"http://www.loushang.org/xmlns/prod/bo/6.0.0\">").append('\n');
		sb.append('\n');
		sb.append("    <xsd:import namespace=\"http://www.loushang.org/xmlns/prod/bo/6.0.0\" schemaLocation=\"BusinessGraph.xsd\"/>").append('\n');
		sb.append("    <xsd:import namespace=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/" + NS_PREFIX + tableName.toLowerCase() + "\" schemaLocation=\"" + table.getFileName() + ".xsd\"/>").append('\n');
		sb.append('\n');
		sb.append("    <xsd:complexType name=\"" + tableName.toUpperCase() + "BG\">").append('\n');
		sb.append("      <xsd:complexContent>").append('\n');
		sb.append("        <xsd:extension base=\"bo:BusinessGraph\">").append('\n');
		sb.append("          <xsd:sequence>").append('\n');
		sb.append("            <xsd:element name=\"verb\" minOccurs=\"0\" maxOccurs=\"1\">").append('\n');
		sb.append("              <xsd:simpleType>").append('\n');
		sb.append("                <xsd:restriction base=\"xsd:string\">").append('\n');
		sb.append("                  <xsd:enumeration value=\"Create\"/>").append('\n');
		sb.append("                  <xsd:enumeration value=\"Update\"/>").append('\n');
		sb.append("                  <xsd:enumeration value=\"Delete\"/>").append('\n');
		sb.append("                </xsd:restriction>").append('\n');
		sb.append("              </xsd:simpleType>").append('\n');
		sb.append("            </xsd:element>").append('\n');
		sb.append("            <xsd:element name=\"" + tableName.toLowerCase() + "\" type=\"" + tableName.toLowerCase() + ":" + tableName.toUpperCase() + "\" minOccurs=\"0\"/>").append('\n');
		sb.append("          </xsd:sequence>").append('\n');
		sb.append("        </xsd:extension>").append('\n');
		sb.append("      </xsd:complexContent>").append('\n');
		sb.append("    </xsd:complexType>").append('\n');
		sb.append('\n');
		sb.append("</xsd:schema>");
		
		PrintWriter out = new PrintWriter(xsdFile);
		out.print(sb.toString());
		out.flush();
		out.close();
		
	}
	/**
	 * 分析 table 的元数据信息
	 * @param conn
	 * @param table
	 * @param db
	 * @return
	 * @exception RuntimeException
	 */
	private List<Line> analyseTable(Connection conn,TableEl table,DbEl db){
		List<Line> csvLines = new ArrayList<Line>();
		// --
		try {
			String catalog = conn.getCatalog();
			String schema = db.getSchema();
			// 判断表是否存在
			DatabaseMetaData dbmd = conn.getMetaData();
			ResultSet rs = dbmd.getTables(catalog, schema,
					                      table.getName(), 
					                      new String[]{"TABLE"});
			if(rs.next()){
				// 获取 table 的主键
				Set<String> pkset = new HashSet<String>();
				ResultSet pkrs = dbmd.getPrimaryKeys(
						              catalog, schema, table.getName());
				while(pkrs.next()){
					String columnName = pkrs.getString("COLUMN_NAME").toUpperCase();
					pkset.add(columnName);
				}
				// 获取 table 的所有列
				ResultSet colrs = dbmd.getColumns(catalog, schema, 
						                          table.getName(), null);
				while(colrs.next()){
					String dataType = colrs.getString("TYPE_NAME").toUpperCase();
					// 获取列的详细信息
					String columnName = colrs.getString("COLUMN_NAME").toUpperCase();
					String xmlType = TypesMapTool.getXmlType(dataType);
					String isPrimaryKey = "false";
					String foreignKey = "";
					// --
					FieldEl field = table.getFields().get(columnName);
					if (pkset.contains(columnName) || (field != null && field.isPrimaryKey())){
						isPrimaryKey = "true";
					}
					if (field != null && field.getForeignKey() != null){
						foreignKey = field.getForeignKey();
					}
					Line line = new Line();
					line.setColumnName(columnName);
					line.setXmlType(xmlType);
					line.setIsPrimaryKey(isPrimaryKey);
					line.setForeignKey(foreignKey);
					if (field != null && field.getUid() != null){
						line.setUid(field.getUid());
					}
					// 
					csvLines.add(line);
				}
			} else {
				// 如果 getTables(catalog, schema, tableName, types) 获取不到
				// 则采用 select * from tableName 的方式获取
				String sql = "select * from " + table.getName() + " where 1=2";
				Statement stmt = conn.createStatement();
				rs = stmt.executeQuery(sql);
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				
				for (int i=1; i<=columnCount; i++){
					String dataType = rsmd.getColumnTypeName(i).toUpperCase();
					// 获取列的详细信息
					String columnName = rsmd.getColumnName(i).toUpperCase();
					String xmlType = TypesMapTool.getXmlType(dataType);
					String isPrimaryKey = "false";
					String foreignKey = "";
					// --
					FieldEl field = table.getFields().get(columnName);
					if ((field != null && field.isPrimaryKey())){
						isPrimaryKey = "true";
					}
					if (field != null && field.getForeignKey() != null){
						foreignKey = field.getForeignKey();
					}
					Line line = new Line();
					line.setColumnName(columnName);
					line.setXmlType(xmlType);
					line.setIsPrimaryKey(isPrimaryKey);
					line.setForeignKey(foreignKey);
					if (field != null && field.getUid() != null){
						line.setUid(field.getUid());
					}
					// 
					csvLines.add(line);
				}
				stmt.close();
				rs.close();
			}
		} catch(Exception e){
			throw new RuntimeException(e.getMessage());
		}
		return csvLines;
	}
	/**
	 * 生成 xsd 文件内容
	 * @param csvLines
	 * @param table
	 * @return
	 */
	private String genTableXsdContent(List<Line> csvLines,TableEl table){
		StringBuilder sb = new StringBuilder();
		List<TableEl> subTables = table.getTables();
		// append header
		appendHeader(sb,table.getName(),subTables);
		// append table header
		appendTableHeader(sb,table.getName());
		// append field
		for (int i=0; i<csvLines.size(); i++){
			Line line = csvLines.get(i);
			appendField(sb,line);
		}
		// append sub table field
		TableEl tmp = null;
		for (int i=0; i<subTables.size(); i++){
			tmp = subTables.get(i);
			appendTableField(sb,tmp);
		}
		// append table tail
		sb.append("      </xsd:sequence>").append('\n');
		sb.append("    </xsd:complexType>").append('\n');
		// append tail
		sb.append("</xsd:schema>");
		return sb.toString();
	}
	/**
	 * 添加 xsd 头信息
	 * @param sb
	 * @param tableName
	 * @param subTables
	 */
	private void appendHeader(StringBuilder sb,String tableName,
			List<TableEl> subTables){
		sb.append("<?xml version=\"1.0\" encoding=\"utf-8\" ?>").append('\n');
		sb.append("<xsd:schema xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"").append('\n');
		sb.append("            targetNamespace=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/" + NS_PREFIX + tableName.toLowerCase() + "\"").append('\n');
		for (int i=0; i<subTables.size(); i++){
			String name = subTables.get(i).getName();
			sb.append("            xmlns:" + name.toLowerCase() + "=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/" + NS_PREFIX + name.toLowerCase() + "\"").append('\n');
		}
		sb.append("            xmlns:jdbcasi=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/metadata\">").append('\n');
		sb.append('\n');
		sb.append("    <xsd:import namespace=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/metadata\" schemaLocation=\"JDBCASI.xsd\"/>").append('\n');
		for (int i=0; i<subTables.size(); i++){
			TableEl t = subTables.get(i);
			sb.append("    <xsd:import namespace=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/" + NS_PREFIX + t.getName().toLowerCase() + "\" schemaLocation=\"" + t.getFileName() + ".xsd\"/>").append('\n');
		}
		sb.append('\n');
	}
	/**
	 * 添加 table 头信息
	 * @param sb
	 * @param tableName
	 */
	private void appendTableHeader(StringBuilder sb,String tableName){
		sb.append("    <xsd:complexType name=\"" + tableName.toUpperCase() + "\">").append('\n');
		sb.append("      <xsd:annotation xml:space=\"preserve\">").append('\n');
		sb.append("        <xsd:appinfo source=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/metadata\">").append('\n');
		sb.append("          <jdbcasi:JDBCBusinessObjectTypeMetadata xmlns:jdbcasi=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/metadata\">").append('\n');
		sb.append("            <jdbcasi:TableName>" + tableName.toUpperCase() + "</jdbcasi:TableName>").append('\n');
		sb.append("          </jdbcasi:JDBCBusinessObjectTypeMetadata>").append('\n');
		sb.append("        </xsd:appinfo>").append('\n');
		sb.append("      </xsd:annotation>").append('\n');
		sb.append('\n');
		sb.append("      <xsd:sequence minOccurs=\"1\" maxOccurs=\"1\">").append('\n');
		sb.append('\n');
	}
	/**
	 * 添加 field 信息
	 * @param sb
	 * @param line
	 */
	private void appendField(StringBuilder sb,Line line){
		if (line == null)return;
		sb.append("      	<xsd:element name=\"" + line.getColumnName().toLowerCase() + "\" type=\"" + line.getXmlType() + "\" nillable=\"true\" minOccurs=\"0\">").append('\n');
		sb.append("      	  <xsd:annotation xml:space=\"preserve\">").append('\n');
		sb.append("             <xsd:appinfo source=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/metadata\">").append('\n');
		sb.append("               <jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/metadata\">").append('\n');
		sb.append("                 <jdbcasi:ColumnName>" + line.getColumnName().toUpperCase() + "</jdbcasi:ColumnName>").append('\n');
		sb.append("                 <jdbcasi:PrimaryKey>" + line.getIsPrimaryKey() + "</jdbcasi:PrimaryKey>").append('\n');
		String fk = line.getForeignKey();
		if (fk != null && !fk.equals("")){
			sb.append("                 <jdbcasi:ForeignKey>" + fk.toLowerCase() + "</jdbcasi:ForeignKey>").append('\n');
		}
		String uid = line.getUid();
		if (uid != null && !uid.equals("")){
			sb.append("                 <jdbcasi:UID>" + uid + "</jdbcasi:UID>").append('\n');
		}
		String xmlType = line.getXmlType();
		if (xmlType.equals("xsd:date")){
			sb.append("                 <jdbcasi:DateType>Date</jdbcasi:DateType>").append('\n');
		} else if (xmlType.equals("xsd:time")){
			sb.append("                 <jdbcasi:DateType>Time</jdbcasi:DateType>").append('\n');
		} else if (xmlType.equals("xsd:dateTime")){
			sb.append("                 <jdbcasi:DateType>TimeStamp</jdbcasi:DateType>").append('\n');
		}
		sb.append("               </jdbcasi:JDBCAttributeTypeMetadata>").append('\n');
		sb.append("             </xsd:appinfo>").append('\n');
		sb.append("           </xsd:annotation>").append('\n');
		sb.append("      	</xsd:element>").append('\n');
		sb.append('\n');
	}
	/**
	 * 添加类型为 table 的 field
	 * @param sb
	 * @param tb
	 */
	private void appendTableField(StringBuilder sb,TableEl tb){
		String tableName = tb.getName();
		String maxOccurs = "unbounded";
		if (!"true".equals(tb.getOwnerShip())){
			maxOccurs = "1";
		}
		sb.append("      	<xsd:element name=\"" + tableName.toLowerCase() + "\" type=\"" + tableName.toLowerCase() + ":" + tableName.toUpperCase() + "\" nillable=\"true\" minOccurs=\"0\" maxOccurs=\"" + maxOccurs + "\">").append('\n');
		sb.append("      	  <xsd:annotation xml:space=\"preserve\">").append('\n');
		sb.append("             <xsd:appinfo source=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/metadata\">").append('\n');
		sb.append("               <jdbcasi:JDBCAttributeTypeMetadata xmlns:jdbcasi=\"http://www.loushang.org/xmlns/prod/j2ca/jdbc/metadata\">").append('\n');
		sb.append("               <jdbcasi:Ownership>" + tb.getOwnerShip() + "</jdbcasi:Ownership>").append('\n');
		sb.append("               </jdbcasi:JDBCAttributeTypeMetadata>").append('\n');
		sb.append("             </xsd:appinfo>").append('\n');
		sb.append("           </xsd:annotation>").append('\n');
		sb.append("      	</xsd:element>").append('\n');
		sb.append('\n');
	}
	
	public ClassLoader getDriverLoader() {
		return driverLoader;
	}
	public void setDriverLoader(ClassLoader driverLoader) {
		this.driverLoader = driverLoader;
	}
}
